package com.huangfuyi.datasource.service;

import com.huangfuyi.common.utils.ObjectUtils;
import com.huangfuyi.datasource.entity.*;
import com.huangfuyi.datasource.entity.ddl.EMySQLCreateDataBaseConf;
import com.huangfuyi.datasource.entity.ddl.EMySQLDataDefineConf;
import com.huangfuyi.datasource.entity.ddl.EMySQLDataQueryConf;
import com.huangfuyi.datasource.entity.query.EDataQueryResult;
import com.huangfuyi.datasource.exceptions.EDDLExecuteException;
import com.huangfuyi.datasource.exceptions.EDMLExecuteException;
import com.huangfuyi.datasource.exceptions.EDataSourceInitException;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.*;
import java.util.stream.Collectors;

// 数据源接口
public class EMySQLDataSource implements EDataSource {

    private static final String TEST_SQL = "select 1";

    private HikariDataSource dataSource;

    private final List<EMySQLConnectConf> allConnectInfo = new ArrayList<>();

    @Override
    public void init(List<? extends EConnectConf> connectInfo) {
        // 检查是否已存在链接，已有链接的情况下，不重新初始化，防止正在执行的操作失败
        if (!ObjectUtils.isEmpty(this.dataSource) && !this.dataSource.isClosed() && queryTest(this.dataSource)) {
            return;
        }
        close();

        if (ObjectUtils.isEmpty(connectInfo)) {
            throw new EDataSourceInitException("传入的链接信息不能为空。");
        }

        for (EConnectConf connInfo : connectInfo) {
            if (!(connInfo instanceof EMySQLConnectConf)) {
                this.allConnectInfo.clear();
                throw new EDataSourceInitException("当前传入的不是MySQL链接信息。");
            }
            EMySQLConnectConf mySQLConnectInfo = (EMySQLConnectConf) connInfo;
            this.allConnectInfo.add(mySQLConnectInfo);
        }

        this.allConnectInfo.sort(Comparator.comparingInt(EMySQLConnectConf::getPriority));

        for (EMySQLConnectConf elem : this.allConnectInfo) {
            try {
                HikariDataSource dataSource = createDataSource(elem);
                if (queryTest(dataSource)) {
                    this.dataSource = dataSource;
                    break;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (ObjectUtils.isEmpty(this.dataSource)) {
            throw new EDataSourceInitException("所有数据源信息都无法建立可用链接。");
        }
    }

    @Override
    public boolean connectionTest(EConnectConf connectInfo) {
        if (!(connectInfo instanceof EMySQLConnectConf)) {
            throw new EDataSourceInitException("当前传入的不是MySQL链接信息。");
        }
        HikariDataSource dataSource = createDataSource((EMySQLConnectConf) connectInfo);
        return queryTest(dataSource);
    }

    @Override
    public void close() {
        if (!ObjectUtils.isEmpty(this.dataSource) && !this.dataSource.isClosed()) {
            try {
                this.dataSource.close();
                this.dataSource = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public Class<? extends EConnectConf> getConnectInfoExample() {
        return EMySQLConnectConf.class;
    }

    @Override
    public int dataDefine(EDataDefineConf defineConf) {
        if (!(defineConf instanceof EMySQLDataDefineConf)) {
            throw new EDataSourceInitException("当前传入的不是MySQL数据定义信息。");
        }

        init(this.allConnectInfo);

        try (Connection conn = this.dataSource.getConnection();
             PreparedStatement statement = conn.prepareStatement(defineConf.toString())) {
            return statement.executeUpdate();
        } catch (Throwable t) {
            throw new EDDLExecuteException(String.format(Locale.ROOT, "MySQL数据定义失败，定义类型：%s， 失败原因：%s", defineConf.getClass().getName(), t.getMessage()), t);
        }
    }

    @Override
    public Class<? extends EDataDefineConf> getDataDefineInfoExample() {
        return EMySQLDataDefineConf.class;
    }

    @Override
    public int insert(EDataDefineConf defineConf, List<Map<String, Object>> data) {
        if (!(defineConf instanceof EMySQLDataDefineConf)) {
            throw new EDMLExecuteException("当前传入的不是MySQL数据定义信息。");
        }

        if (ObjectUtils.isEmpty(data)) {
            return 0;
        }

        init(this.allConnectInfo);

        EMySQLDataDefineConf mySQLDataDefineConf = (EMySQLDataDefineConf) defineConf;

        ArrayList<String> columns = new ArrayList<>(data.get(0).keySet());
        String columnStr = columns.stream().map(String::valueOf).collect(Collectors.joining(","));
        ArrayList<String> paramSite = new ArrayList<>();
        for (int i = 0; i < columns.size(); i++) {
            paramSite.add("?");
        }
        String paramSiteStr = paramSite.stream().map(String::valueOf).collect(Collectors.joining(","));
        String sql = String.format(Locale.ENGLISH, "insert into %s.%s (%s) values (%s)", mySQLDataDefineConf.getDatabase(), mySQLDataDefineConf.getTable(), columnStr, paramSiteStr);

        try (Connection connection = this.dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            for (Map<String, Object> dataElem : data) {
                int columnIndex = 1;
                for (String column : columns) {
                    statement.setObject(columnIndex++, dataElem.get(column));
                }
                statement.addBatch();
            }
            int[] ints = statement.executeBatch();
            return Arrays.stream(ints).sum();
        } catch (Throwable t) {
            throw new EDMLExecuteException(String.format(Locale.ROOT, "数据写入失败，失败原因：%s", t.getMessage()), t);
        }
    }

    @Override
    public int delete(EDataDefineConf defineConf, EDataQueryCondition condition) {
        if (!(defineConf instanceof EMySQLDataDefineConf)) {
            throw new EDMLExecuteException("当前传入的不是MySQL数据定义信息。");
        }

        init(this.allConnectInfo);

        EMySQLDataDefineConf mySQLDataDefineConf = (EMySQLDataDefineConf) defineConf;

        if (ObjectUtils.isEmpty(condition) || condition.isEmptyCondition()) {
            throw new EDMLExecuteException("MySQL数据库的删除操作条件不能为空。");
        }

        ArrayList<Object> wherePparams = new ArrayList<>();
        String whereStr = makeWhereStr(condition, wherePparams);

        String sql = String.format(Locale.ENGLISH, "delete from %s.%s where %s", mySQLDataDefineConf.getDatabase(), mySQLDataDefineConf.getTable(), whereStr);

        try (Connection connection = this.dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {


            int columnIndex = 1;
            for (Object whereValue : wherePparams) {
                statement.setObject(columnIndex++, whereValue);
            }
            return statement.executeUpdate();
        } catch (Throwable t) {
            throw new EDMLExecuteException(String.format(Locale.ROOT, "数据删除失败，失败原因：%s", t.getMessage()), t);
        }
    }

    @Override
    public int update(EDataDefineConf defineConf, Map<String, Object> data, EDataQueryCondition condition) {
        if (!(defineConf instanceof EMySQLDataDefineConf)) {
            throw new EDMLExecuteException("当前传入的不是MySQL数据定义信息。");
        }

        init(this.allConnectInfo);

        EMySQLDataDefineConf mySQLDataDefineConf = (EMySQLDataDefineConf) defineConf;
        if (ObjectUtils.isEmpty(data)) {
            throw new EDMLExecuteException("MySQL数据库的更新操作待更新数据不能为空。");
        }
        if (ObjectUtils.isEmpty(condition) || condition.isEmptyCondition()) {
            throw new EDMLExecuteException("MySQL数据库的更新操作条件不能为空。");
        }

        ArrayList<Object> whereParams = new ArrayList<>();
        String whereStr = makeWhereStr(condition, whereParams);

        ArrayList<Object> setParam = new ArrayList<>();
        StringBuilder setStr = new StringBuilder();
        for (Map.Entry<String, Object> me : data.entrySet()) {
            setStr.append(me.getKey()).append(" = ?").append(",");
            setParam.add(me.getValue());
        }
        setStr.deleteCharAt(setStr.length() - 1);


        String sql = String.format(Locale.ENGLISH, "update %s.%s set %s where %s", mySQLDataDefineConf.getDatabase(), mySQLDataDefineConf.getTable(), setStr.toString(), whereStr);

        try (Connection connection = this.dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {

            int columnIndex = 1;
            for (Object setValue : setParam) {
                statement.setObject(columnIndex++, setValue);
            }

            for (Object whereValue : whereParams) {
                statement.setObject(columnIndex++, whereValue);
            }
            return statement.executeUpdate();
        } catch (Throwable t) {
            throw new EDMLExecuteException(String.format(Locale.ROOT, "数据更新失败，失败原因：%s", t.getMessage()), t);
        }
    }

    @Override
    public EDataQueryResult select(EDataDefineConf defineConf, EDataSelectConf selectConfig) {
        return this.select(defineConf, selectConfig, null);
    }

    @Override
    public EDataQueryResult select(EDataDefineConf defineConf, EDataSelectConf selectConfig, EDataQueryCondition condition) {
        init(this.allConnectInfo);

        if (selectConfig.getPageNum() <= 0) {
            selectConfig.setPageNum(1);
        }
        if (selectConfig.getPageSize() <= 0 || selectConfig.getPageSize() > 100) {
            selectConfig.setPageSize(10);
        }
        int start = (selectConfig.getPageNum() - 1) * selectConfig.getPageSize();

        String columnStr;
        List<String> columns = selectConfig.getColumnList();
        if (ObjectUtils.isEmpty(columns)) {
            columnStr = "*";
        } else {
            columnStr = String.join(",", columns);
        }

        String sql;
        ArrayList<Object> params = null;
        if (ObjectUtils.isEmpty(condition) || condition.isEmptyCondition()) {
            sql = String.format(Locale.ENGLISH, "select %s from %s.%s limit %d,%d", columnStr, defineConf.getDatabase(), defineConf.getTable(), start, selectConfig.getPageSize());
        } else {
            params = new ArrayList<>();
            String whereStr = makeWhereStr(condition, params);
            sql = String.format(Locale.ENGLISH, "select %s from %s.%s where %s limit %d,%d", columnStr, defineConf.getDatabase(), defineConf.getTable(), whereStr, start, selectConfig.getPageSize());
        }

        try (Connection connection = this.dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            int columnIndex = 1;
            if (!ObjectUtils.isEmpty(params)) {
                for (Object setValue : params) {
                    statement.setObject(columnIndex++, setValue);
                }
            }
            ResultSet resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();

            ArrayList<Map<String, Object>> result = new ArrayList<>();
            while (resultSet.next()) {
                HashMap<String, Object> dataElem = new HashMap<>();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    dataElem.put(metaData.getColumnLabel(0), resultSet.getObject(i));
                }
                result.add(dataElem);
            }
            return result;
        } catch (Throwable t) {
            throw new EDMLExecuteException(String.format(Locale.ROOT, "数据更新失败，失败原因：%s", t.getMessage()), t);
        }
    }

    @Override
    protected void finalize() throws Throwable {
        super.finalize();
        close();
    }

    private HikariDataSource createDataSource(EMySQLConnectConf connInfo) {
        HikariConfig config = new HikariConfig();
        config.setPoolName(connInfo.getId());
        config.addDataSourceProperty("user", connInfo.getUserName());
        config.addDataSourceProperty("password", connInfo.getPassword());
        config.setJdbcUrl(String.format(Locale.ENGLISH, "jdbc:mysql://%s:%d?%s",
                connInfo.getIp(),
                connInfo.getPort(),
                connInfo.getConnectConfig().toString()));
        config.setMaximumPoolSize(connInfo.getMaxPoolSize());

        return new HikariDataSource(config);
    }

    private boolean queryTest(HikariDataSource dataSource) {
        try (Connection connect = dataSource.getConnection();
             PreparedStatement statement = connect.prepareStatement(TEST_SQL);
             ResultSet resultSet = statement.executeQuery()) {
            resultSet.next();
            int testResult = resultSet.getInt(1);
            if (testResult == 1) {
                return true;
            }
        } catch (Throwable t) {
            t.printStackTrace();
            return false;
        }
        return false;
    }

    private String makeWhereStr(EDataQueryCondition whereConf, List<Object> params) {
        if (!ObjectUtils.isEmpty(whereConf.getField())
                && !ObjectUtils.isEmpty(whereConf.getDataCompare())) {
            params.add(whereConf.getValue());
            switch (whereConf.getDataCompare()) {
                case EQ:
                case NOT_EQ:
                case GT:
                case GTE:
                case LT:
                case LTE:
                    return String.format(Locale.ENGLISH, "%s %s ?", whereConf.getField(), whereConf.getDataCompare().getSymbol());
                case NULL:
                case NOT_NULL:
                    return String.format(Locale.ENGLISH, "%s %s", whereConf.getField(), whereConf.getDataCompare().getSymbol());
                default:
                    throw new EDMLExecuteException(String.format(Locale.ROOT, "不支持的比较操作符：%s", whereConf.getDataCompare().toString()));
            }
        } else if (!ObjectUtils.isEmpty(whereConf.getConditionCompare())
                && !ObjectUtils.isEmpty(whereConf.getSubEDataCondition())) {
            ArrayList<String> whereStrs = new ArrayList<>();
            for (EDataQueryCondition subWhere : whereConf.getSubEDataCondition()) {
                whereStrs.add(String.format(Locale.ENGLISH, "( %s )", makeWhereStr(subWhere, params)));
            }
            return whereStrs.stream().collect(Collectors.joining(String.format(Locale.ENGLISH, " %s ", whereConf.getConditionCompare().getSymbol())));
        } else {
            throw new EDMLExecuteException(String.format(Locale.ROOT, "MySQL条件配置错误。错误配置：%s", whereConf.toString()));
        }
    }
}
